package com.ncloud.timetask.dashboard.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author sundelong
 * project_name: timetask 
 * type_name: DashboardAdminTaskMapper 
 * date: 2017年6月21日下午5:35:55 
 * 
 */

@Mapper
public interface DashboardAdminTaskMapper {
	/*管理门户---月度收入：当前自然年内，云网平台每月收入（渠道结算金额）  ----task_id=1001*/
	//本月收入
	@Select(value = ("SELECT SUM(balance) AS sum FROM(select discnt_id,balance,create_time,discnt_inst_id from "
			+ "bs_discnt_fee UNION select c.discnt_id,"
			+ "c.balance,c.create_time,discnt_inst_id from bs_o_discnt_fee c,bs_o_order d where c.order_id "
			+ "= d.order_id) a WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')"))
	public String  getSumBalanceByThisMonth();

	/*战区销售量统计：
	*/
	//本月战区销售量统计
	@Select(value = ("SELECT r.chnl_name,r.chnl_id,l.sum FROM sys_chnl r LEFT JOIN (SELECT chnl_id,SUM(balance) AS SUM"
			+ " FROM (SELECT chnl_id,user_id FROM sys_sales) a ,(SELECT discnt_id,discnt_inst_id,create_user,"
			+ "balance,create_time FROM bs_discnt_fee  UNION SELECT c.discnt_id,c.discnt_inst_id,c.create_user,"
			+ "c.balance,c.create_time FROM bs_o_discnt_fee c,bs_o_order d WHERE c.order_id = d.order_id) b "
			+ "WHERE a.user_id=b.create_user AND DATE_FORMAT(b.create_time,'%Y%m') = DATE_FORMAT(CURDATE(),"
			+ "'%Y%m') GROUP BY chnl_id ORDER BY SUM DESC)l ON r.chnl_id=l.chnl_id"))
	public List<Map<Object,Object>>  getSumBalanceByChnlAndThisMonth();	
	
	//本年内各个套餐的销售金额	
	@Select(value = ("SELECT f_get_prodName(b.prod_id) AS prod_name, b.discnt_name, SUM(a.balance) AS sum "
			+ " FROM (select discnt_id,balance,create_time,discnt_inst_id from bs_discnt_fee "
			+ " where NOW() BETWEEN begin_date and end_date "
			+ " UNION select c.discnt_id,c.balance,c.create_time,discnt_inst_id "
			+ " from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id and c.MODIFY_TAG = '1') a,"
			+ " bs_p_discnt b WHERE	YEAR (create_time) = YEAR (NOW()) AND a.discnt_id = b.discnt_id"
			+ " GROUP BY prod_name, discnt_name"))
	public List<Map<Object,Object>> getCountDiscnt();	
	
	//本年内各个产品的销售金额
	@Select(value = ("SELECT f_get_prodName (b.prod_id) AS prod_name,SUM(a.balance) AS fee "
			+ " FROM (select discnt_id,balance,create_time,discnt_inst_id from bs_discnt_fee"
			+ " where NOW() BETWEEN begin_date and end_date UNION select c.discnt_id,c.balance,c.create_time,discnt_inst_id"
			+ " from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id and c.MODIFY_TAG = '1') a,bs_p_discnt b"
			+ " WHERE a.discnt_id = b.discnt_id AND YEAR(a.create_time) = YEAR(NOW()) GROUP BY prod_name"))
	public List<Map<Object,Object>> getCountByProduct();
	
	//本月产品结算单的总额（渠道结算费用）
	@Select(value = (" SELECT f_get_prodName(b.prod_id) AS prod_name, SUM(a.balance) AS fee,c.prod_type"
			+ "	FROM (select discnt_id,balance,create_time,discnt_inst_id from bs_discnt_fee"
			+ " where NOW() BETWEEN begin_date and end_date UNION select c.discnt_id,c.balance,c.create_time,discnt_inst_id"
			+ " from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id and c.MODIFY_TAG = '1') a ,"
			+ " bs_p_discnt b ,bs_p_product c WHERE a.discnt_id=b.discnt_id AND b.prod_id=c.prod_id"
			+ " AND DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY prod_name,prod_type"))
	public List<Map<Object,Object>> getSettlementByProduct();
	
	
	
	
	//查询数据是否存在
	@Select(value = (" select count(*) as count from bs_monthly_task where task_id = #{0} and year = #{1} and  month = #{2} and task_key = #{3}"))
	public int getExistence2(String taskId,String year,String month,String taskKey);
	
	//查询数据是否存在
	@Select(value = (" select count(*) as count from bs_monthly_task where task_id = #{0} and year = #{1} and  month = #{2} and task_key = #{3} and task_value2=#{4}"))
	public int getExistence3(String taskId,String year,String month,String taskKey,String taskValue2);
	//月度查询任务插入bs_monthly_task表
	@Insert(value = ("insert into bs_monthly_task (task_id,year,month,task_key,task_value,create_time,update_time,remark,task_value2,task_value3) values "
			+ "(#{0},#{1},#{2},#{3},#{4},now(),now(),#{5},#{6},#{7})"))
	public int insertBsMonthlyTask(String taskId,String year,String month,String taskKey,String taskValue,String remark,String taskValue2,String taskValue3);
	
	@Update(value = ("update bs_monthly_task set task_value = #{0},update_time=now() where task_id = #{1} and year = #{2} and month = #{3} and task_key = #{4}"))
	public int updateBsMonthlyTask2(String taskValue,String taskId,String year,String month,String taskKey);
	
	//查询数据是否存在
	@Select(value = (" select count(*) as count from bs_monthly_task where task_id = #{0} and year = #{1} and  month = #{2} and task_key = #{3} and task_value2=#{4}"))
	public int getExistence(String taskId,String year,String month,String taskKey,String taskValue2);
	
	//更新本月的数据
	@Update(value = ("update bs_monthly_task set task_value = #{0},update_time=now() where task_id = #{1} and year = #{2} and month = #{3} and task_key = #{4} and task_value2=#{5}"))
	public int updateBsMonthlyTask(String taskValue,String taskId,String year,String month,String taskKey,String taskValue2);
	
	
	
}
